/*
 * Created by SharpDevelop.
 * User: florian
 * Date: 17.06.2006
 * Time: 10:20
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.ComponentModel;
using System.Text;
using System.Data;


namespace SqlScriptCreator.Generators 
{
	/// <summary>
	/// Description of InsertTableVariableGenerator.
	/// </summary>
	[DefaultPropertyAttribute("TableName")]
	public class InsertTableVariableGenerator : IScriptGenerator
	{
		private String _tableName = "myTable";
		
		[CategoryAttribute("Table settings"),
		 DescriptionAttribute("The name of the generated table variable (@TABLENAME).")]
		public string TableName {
			get {
				return _tableName;
			}
			set {
				_tableName = value;
			}
		}
		
		private String _columnPrefix;
		
		[CategoryAttribute("Table settings"),
		 DescriptionAttribute("Characters that will be prepended to every generated table column.")]
		public string ColumnPrefix {
			get {
				return _columnPrefix;
			}
			set {
				_columnPrefix = value;
			}
		}
		
		private String _columnSuffix;
		
		[CategoryAttribute("Table settings"),
		 DescriptionAttribute("Characters that will be appended to every generated table column.")]
		public string ColumnSuffix {
			get {
				return _columnSuffix;
			}
			set {
				_columnSuffix = value;
			}
		}
		
		public InsertTableVariableGenerator()
		{
		}
		
		override public string ToString() {
			return "Insert into table-variable";
		}

        public string generateScript(String query, DataTable dtQueryResult, DataTable dtParameters)
        {		
            StringBuilder sb = new StringBuilder();
            String[] fieldnames = new String[dtParameters.Rows.Count];
            String lineend;
            String lengthSpecification;

            sb.AppendLine("DECLARE @"+TableName+" TABLE (");
            for(int i=0; i<dtParameters.Rows.Count;i++)
            {
            	DataRow row = dtParameters.Rows[i];
                int columnSize = Convert.ToInt32(row["ColumnSize"]);
                String dataType = row["DataTypeName"].ToString();
                String columnName = row["BaseColumnName"].ToString();

                if (i+1 < dtParameters.Rows.Count)
                	lineend = ",";
                else 
                	lineend = "";

                fieldnames[i] = ColumnPrefix + columnName + ColumnSuffix;
                sb.AppendLine("\t" + fieldnames[i] + " " + SqlTools.getDatatypeExpression(dataType, columnSize)
                    + lineend);
            }
            sb.AppendLine(")");
            sb.AppendLine("");
            sb.Append("INSERT INTO @"+TableName+" (");
            sb.Append(String.Join(", ", fieldnames));
            sb.AppendLine(")");
            sb.AppendLine(query);
            sb.AppendLine("");
            sb.AppendLine("SELECT * FROM @"+TableName);
            
            return sb.ToString();			
		}
	}
}
